﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;

namespace Ylh.SQLServerDAL
{
    public class CustServerDAL : ICustServer
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int InserServer(string ConStr, Ylh.Model.TbCustServer model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbCustServer(");
            strSql.Append("CustID,StaffID,ServerTime,FollowTime,ReportSendTime,Feedback,ServerDetailed,Remark)");
            strSql.Append(" values (");
            strSql.Append("@CustID,@StaffID,@ServerTime,@FollowTime,@ReportSendTime,@Feedback,@ServerDetailed,@Remark)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@ServerTime", SqlDbType.DateTime),
					new SqlParameter("@FollowTime", SqlDbType.DateTime),
					new SqlParameter("@ReportSendTime", SqlDbType.DateTime),
					new SqlParameter("@Feedback", SqlDbType.VarChar,500),
					new SqlParameter("@ServerDetailed", SqlDbType.VarChar,500),
					new SqlParameter("@Remark", SqlDbType.Text)};
            parameters[0].Value = model.CustID;
            parameters[1].Value = model.StaffID;
            parameters[2].Value = model.ServerTime;
            parameters[3].Value = model.FollowTime;
            parameters[4].Value = model.ReportSendTime;
            parameters[5].Value = model.Feedback;
            parameters[6].Value = model.ServerDetailed;
            parameters[7].Value = model.Remark;
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            return Convert.ToInt32(dt.Rows[0][0]);

        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateServer(string ConStr, Ylh.Model.TbCustServer model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbCustServer set ");
            strSql.Append("CustID=@CustID,");
            strSql.Append("StaffID=@StaffID,");
            strSql.Append("ServerTime=@ServerTime,");
            strSql.Append("FollowTime=@FollowTime,");
            strSql.Append("ReportSendTime=@ReportSendTime,");
            strSql.Append("Feedback=@Feedback,");
            strSql.Append("ServerDetailed=@ServerDetailed,");
            strSql.Append("Remark=@Remark");
            strSql.Append(" where CustServerID=@CustServerID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustServerID", SqlDbType.Int,4),
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@ServerTime", SqlDbType.DateTime),
					new SqlParameter("@FollowTime", SqlDbType.DateTime),
					new SqlParameter("@ReportSendTime", SqlDbType.DateTime),
					new SqlParameter("@Feedback", SqlDbType.VarChar,500),
					new SqlParameter("@ServerDetailed", SqlDbType.VarChar,500),
					new SqlParameter("@Remark", SqlDbType.Text)};
            parameters[0].Value = model.CustServerID;
            parameters[1].Value = model.CustID;
            parameters[2].Value = model.StaffID;
            parameters[3].Value = model.ServerTime;
            parameters[4].Value = model.FollowTime;
            parameters[5].Value = model.ReportSendTime;
            parameters[6].Value = model.Feedback;
            parameters[7].Value = model.ServerDetailed;
            parameters[8].Value = model.Remark;
            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;

        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteServerInfo(string ConStr, int CustServerID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete TbCustServer ");
            strSql.Append(" where CustServerID=@CustServerID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustServerID", SqlDbType.Int,4)};
            parameters[0].Value = CustServerID;

            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;
        }



        /// <summary>
        /// 查找所有数据
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID"></param>
        /// <returns></returns>
        public DataTable SelectServerInfo(string ConStr, int CustID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *    from  TbCustServer ");
            strSql.Append(" where CustID=@CustID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustID", SqlDbType.Int,4)};
            parameters[0].Value = CustID;

            return SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);
        }



        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Ylh.Model.TbCustServer GetModel( string ConStr,  int CustServerID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 CustServerID,CustID,StaffID,ServerTime,FollowTime,ReportSendTime,Feedback,ServerDetailed,Remark from TbCustServer ");
            strSql.Append(" where CustServerID=@CustServerID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustServerID", SqlDbType.Int,4)};
            parameters[0].Value = CustServerID;

            Ylh.Model.TbCustServer model = new Ylh.Model.TbCustServer();
           
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["CustServerID"].ToString() != "")
                {
                    model.CustServerID = int.Parse(dt.Rows[0]["CustServerID"].ToString());
                }
                if (dt.Rows[0]["CustID"].ToString() != "")
                {
                    model.CustID = int.Parse(dt.Rows[0]["CustID"].ToString());
                }
                if (dt.Rows[0]["StaffID"].ToString() != "")
                {
                    model.StaffID = int.Parse(dt.Rows[0]["StaffID"].ToString());
                }
                if (dt.Rows[0]["ServerTime"].ToString() != "")
                {
                    model.ServerTime = DateTime.Parse(dt.Rows[0]["ServerTime"].ToString());
                }
                if (dt.Rows[0]["FollowTime"].ToString() != "")
                {
                    model.FollowTime = DateTime.Parse(dt.Rows[0]["FollowTime"].ToString());
                }
                if (dt.Rows[0]["ReportSendTime"].ToString() != "")
                {
                    model.ReportSendTime = DateTime.Parse(dt.Rows[0]["ReportSendTime"].ToString());
                }
                model.Feedback = dt.Rows[0]["Feedback"].ToString();
                model.ServerDetailed = dt.Rows[0]["ServerDetailed"].ToString();
                model.Remark = dt.Rows[0]["Remark"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
    }
}
